Mega Code Archive - Oracle PLSQL Tutorial

PL SQL Statements
1) A complete example if uisng the cursor variable using a cursor FOR LOOP
2) A complete example of using the cursor variable using a WHILE LOOP
3) A Full, Explicit Rollback
4) A nested block which declares and prints a variable of the same name
5) A Simple Condition Statement
6) A Simple Condition Statement with BOOLEAN variable
7) A SQL Replacement for Regular Loops
8) Adding a limit to BULK COLLECT
9) Adding a Partial Rollback
10) An example of code using native dynamic SQL
11) An example of comparison of two numbers using a searched CASE expression
12) An Example of the GOTO Statement
13) Audit as Autonomous Transaction
14) Autonomous Transactions
15) Block IF statement
16) Build an anonymous block that will trigger an error
17) Building DDL on the Fly
18) Building SQL on the Fly
19) Bulk collect from cursor with LIMIT option
20) Bulk collect with non-cursor SELECT into multiple collections
21) Bulk processing with the FORALL statement
22) Calculate circle Area in WHILE loop
23) Call EXECUTE IMMEDIATE in PlSQL block
24) Call function and get result by using EXECUTE IMMEDIATE
25) Call function in PLSQL
26) Case statement to call procedure
27) CASE statements
28) Case when
29) Catch exception from EXECUTE IMMEDIATE
30) Check condition first, then raise exception
31) Commit an Insert statement in a pragma autonomous_transaction procedure
32) Comparing with NULL
33) Conditional Logic
34) Count up by hundreds until we get an error
35) Create a function and call it in an if statement
36) Create a function to count table row
37) Create a loop that prints a list of all employees and their managers
38) Create dynamic table
39) Creating a REPEAT UNTIL Loop
40) DDL doesnt work with PLSQL
41) Define a procedure to drop a database object
42) Define local exceptioon variable
43) Demo a SELECT INTO with exception handling
44) Demonstrate GOTO
45) Demonstrate how to use DELETE with bulk processing
46) Drop any objects to make script re-runnable
47) Drop user, create user and grant permission with PLSQL code
48) Dynamic DDL
49) Dynamic DQL with DBMS_SQL package
50) Dynamic PLSQL
51) ELSIF Ladder
52) Example of a WHILE loop that never executes
53) EXECUTE IMMEDIATE dynamic sql to alter session
54) EXECUTE IMMEDIATE USING IN
55) Execute immediate in action
56) Execute immediate into
57) Execute sql statement in a procedure
58) EXIT a FOR LOOP
59) EXIT a FOR LOOP with exit command
60) EXIT a LOOP for a certain condition
61) EXIT a LOOP WHEN a certain condition
62) EXIT a WHILE LOOP
63) EXIT a WHILE LOOP with exit command
64) EXIT statement with Label
65) EXIT WHEN
66) Exit a function
67) Exit a LOOP
68) Exit to a Label
69) Exiting from Nested Loops
70) EXPLAIN PLAN FOR select statement
71) Fetch Cursor data in LOOP
72) Firing explicit and implicit commits
73) FOR Loop Ranges
74) FOR Loop Scoping Rules
75) For loop variable in a range of numbers
76) FOR LOOP with label
77) FOR LOOP with loop counter
78) FOR Loops
79) FOR year_number IN 1800 1995
80) Forall in indices of
81) Getting a value with a RETURNING clause
82) GOTO statement
83) Handling conditions
84) If block statement
85) IF ELSE statements
86) IF ELSIF ladder
87) IF with ELSE
88) Implicit COMMITS in DDL Statements
89) Implicit commits
90) JUMP out of a IF statement
91) JUMP out of a IF statement with goto
92) Labeled Block
93) Labeled statement
94) Labels and the GOTO Statement
95) LOOP END LOOP
96) LOOP with Labels
97) Loops
98) Mark an anonymous block pragma autonomous_transaction
99) Multiple-row SELECT command
100) Multiple-Row SELECT Command with Several Exception-Handling Routines
101) Named case block
102) Named loop block
103) Nested Blocks
104) Nested FOR loop counter
105) Nested FOR LOOP with Labels
106) Nested IF statement in For loop
107) Nested loops
108) Nested three level LOOP
109) Nested WHILE Loop
110) Nesting Functions and Procedures
111) Non-Working p_log_audit
112) NULL statement
113) Passing several bind variables
114) PLW-06002
115) Pragma autonomous_transaction package member procedure
116) Quotation string
117) Raise the exception
118) Re-raise the exception to indicate there is a problem
119) Retrieving a single row
120) Return statement with case
121) Reusing Bind Variables in Dynamic PLSQL
122) Reusing Bind Variables in Dynamic SQL
123) Reversed FOR LOOP
124) Reversed loop
125) Roll back in exception handler
126) Select BULK COLLECT
127) Select count value into a variable
128) Select data into PLSQL table of cursor
129) Select data into record type data
130) Select into and subquery
131) SELECT into ROWTYPE
132) Select into rowtype then use it in execute immediate
133) SELECT into TYPE
134) Select pair value into two variables
135) Simple CASE statement with range
136) Simple EXECUTE IMMEDIATE
137) Simple Loops
138) Speeding Up Data Collection with Bulk Operations
139) Table row counter
140) The EXIT and EXIT WHEN Statements
141) The EXIT WHEN statement can appear anywhere in the loop code
142) The FORALL command builds a set of SQL statements and executes all of them at once
143) The IF THEN ELSE Statement
144) The structure of a named block
145) The Syntax for IF ELSIF
146) The Syntax for Nested IF Statements
147) The upper or lower bounds of the FOR loop can be defined as variables or functions
148) This is an example of nested anonymous blocks
149) Three valued comparison
150) Timing a loop
151) Timing loop in another loop
152) To get the same result by using explicit cursors and bulk collect
153) Understanding bind variable types
154) Update row with execute immediate
155) Use CASE statement
156) Use case statement in a dbms_output put_line
157) Use case statement in procedure call to use the proper parameter value
158) Use EXECUTE IMMEDIATE to execute an update statement
159) Use execute immediate to run a insert statement
160) Use for all to insert value to table collection of rowtype
161) Use FOR LOOP to loop through dates
162) Use forall to loop through first element to last element in a table collection of number
163) Use IF THEN ELSE IF
164) Use IF-THEN-ELSE to check the return value from the select into statement
165) Use if statement to check the selected into value
166) Use if with IN
167) Use LOOP to insert data to a table
168) Use more than one entrances in if statement to handle the value returned from a select into
169) Use procedure to create an index dynamically
170) Use quotation with a bind variable
171) Use rollback in PLSQL
172) Use Select into statement to assign value to a variable
173) Use VALUES OF
174) Use VALUES OF with undefined row
175) Use WHILE Loop to insert data to table
176) Using a NULL bind variable
177) Using an ELSIF Statement
178) Using Bind Variables in Dynamic SQL
179) Using EXIT WHEN in a loop
180) Using Labels and EXIT Statements with Loops
181) Using Multiple Bind Variables
182) Using Quoted Strings with Dynamic SQL
183) Using the RETURNING BULK COLLECT clause to SELECT directly into a PLSQL array
184) Variable assignment with case statement
185) Variable-delimitted loop
186) When creating selector CASE statements, you cannot have NULL in the list of possible values
187) WHILE LOOP with counter
188) WHILE Loops
189) While Counter is initialized to NULL
190) While loop
191) Wrap EXECUTE IMMEDIATE for current user
192) Wrap statement with BEGIN END
193) You must have the same datatypes in both the SELECT and INTO clauses